package com.seafood.repository;

import com.seafood.entity.ColdChainAlert;
import com.seafood.entity.ColdChainRecord;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 冷链报警Repository
 */
@Repository
public interface ColdChainAlertRepository extends JpaRepository<ColdChainAlert, Long> {
    
    /**
     * 根据冷链记录查找报警
     */
    List<ColdChainAlert> findByColdChainRecord(ColdChainRecord coldChainRecord);
    
    /**
     * 根据冷链记录ID查找报警，按时间倒序
     */
    List<ColdChainAlert> findByColdChainRecordIdOrderByAlertTimeDesc(Long coldChainRecordId);
    
    /**
     * 查找未解决的报警
     */
    List<ColdChainAlert> findByColdChainRecordAndIsResolvedFalse(ColdChainRecord coldChainRecord);
    
    /**
     * 查找所有未解决的报警
     */
    @Query("SELECT a FROM ColdChainAlert a WHERE a.isResolved = false ORDER BY a.alertTime DESC")
    Page<ColdChainAlert> findAllUnresolvedAlerts(Pageable pageable);
    
    /**
     * 根据报警类型查找
     */
    List<ColdChainAlert> findByAlertType(ColdChainAlert.AlertType alertType);
    
    /**
     * 根据严重程度查找
     */
    List<ColdChainAlert> findBySeverity(ColdChainAlert.Severity severity);
    
    /**
     * 查找高严重程度的未解决报警
     */
    @Query("SELECT a FROM ColdChainAlert a WHERE a.isResolved = false " +
           "AND a.severity IN ('HIGH', 'CRITICAL') ORDER BY a.alertTime DESC")
    List<ColdChainAlert> findHighSeverityUnresolvedAlerts();
    
    /**
     * 根据时间范围查找报警
     */
    @Query("SELECT a FROM ColdChainAlert a WHERE a.alertTime >= :startTime AND a.alertTime <= :endTime")
    List<ColdChainAlert> findByTimeRange(@Param("startTime") LocalDateTime startTime,
                                        @Param("endTime") LocalDateTime endTime);
    
    /**
     * 查找未发送通知的报警
     */
    List<ColdChainAlert> findByNotificationSentFalse();
    
    /**
     * 统计各类型报警数量
     */
    @Query("SELECT a.alertType, COUNT(a) FROM ColdChainAlert a GROUP BY a.alertType")
    List<Object[]> countByAlertType();
    
    /**
     * 统计各严重程度报警数量
     */
    @Query("SELECT a.severity, COUNT(a) FROM ColdChainAlert a GROUP BY a.severity")
    List<Object[]> countBySeverity();
    
    /**
     * 查找指定冷链记录的未解决报警数量
     */
    @Query("SELECT COUNT(a) FROM ColdChainAlert a WHERE a.coldChainRecord.id = :recordId AND a.isResolved = false")
    Long countUnresolvedAlerts(@Param("recordId") Long recordId);
    
    /**
     * 查找最近的报警
     */
    Page<ColdChainAlert> findByOrderByAlertTimeDesc(Pageable pageable);
    
    /**
     * 删除指定时间之前的已解决报警
     */
    @Query("DELETE FROM ColdChainAlert a WHERE a.isResolved = true AND a.resolvedTime < :cutoffTime")
    void deleteOldResolvedAlerts(@Param("cutoffTime") LocalDateTime cutoffTime);
}